const express = require("express");
const residentsRouter = express.Router();
const upload = require("./../db/uploadconfig");
const DB = require("./../db/config");

module.exports = residentsRouter;
/**
 * 添加居民类别
 * @param {String} ptName 居民类别名称  required,
 * @param {String} ptDes 居民类别描述 required
 *
 */
residentsRouter.route("/residents/type/add").post((req, res) => {
  let { ptName, ptDes } = req.body;
  let sql = "insert into residents_types (rtname,rtdes) values(?,?);";
  let param = [ptName, ptDes];
  let cb = (err, data) => {
    if (!err) {
      res.json({ code: 0, msg: "添加居民类别成功", data: [] });
    } else {
      res.json({ code: 4002, msg: "添加居民类别失败", data: [] });
    }
  };
  DB.connect(sql, param, cb);
});
/**
 * 获取所有居民类别数据
 */
residentsRouter.route("/residents/type/list").get((req, res) => {
  let sql = "select * from residents_types;";
  let cb = (err, data) => {
    if (!err) {
      res.json({ code: 0, msg: "查询所有居民类别成功", data });
    } else {
      res.json({ code: 4002, msg: "查询所有居民类别失败", data });
    }
  };
  DB.connect(sql, [], cb);
});
/**
 * 修改居民类别
 * @param {Number} rtid 居民类别ID requred
 * @param {String} rtdes 居民类别描述 requred
 * @param {String} rtname 居民类别名字 requred
 */
residentsRouter.route("/residents/type/update").post((req, res) => {
  let { rtid, ptName, ptDes } = req.body;
  rtid = Number(rtid);
  let param = [ptName, ptDes, rtid];
  let sql = "update residents_types set rtname = ?, rtdes = ? where rtid = ?;";
  let cb = (err, data) => {
    if (!err) {
      res.json({ code: 0, msg: "修改居民类别成功。", data: [] });
    } else {
      res.json({ code: 4002, msg: "修改居民类别失败。", data: [] });
    }
  };
  DB.connect(sql, param, cb);
});
/**
 * 删除居民类别
 * @param {Number} rtid 居民类别ID required
 */
residentsRouter.route("/residents/type/delete").post((req, res) => {
  let rtid = req.body.rtid;
  rtid = Number(rtid);

  let param = [rtid];
  let sql = "delete from residents_types where rtid = ?;";
  let cb = (err, data) => {
    console.log(err);
    if (!err) {
      res.json({ code: 0, msg: "删除居民类别成功", data: [] });
    } else {
      res.json({ code: 4002, msg: "删除居民类别失败", data: [] });
    }
  };
  DB.connect(sql, param, cb);
});

/**
 * 添加居民信息
 */
residentsRouter
  .route("/residents/add")
  .post(upload.single("rphoto"), (req, res) => {
    let {
      rname,
      rsex,
      rsid,
      rtel,
      raddress,
      tid,
      rtid,
      rstate,
      buyService,
      did,
    } = req.body;
    rtid = JSON.parse(rtid);
    buyService = JSON.parse(buyService);
    let rphoto = req.file.filename;
    let rid = ""; //居民ID

    new Promise((resolve, reject) => {
      let sql = `insert into residents_table values(NULL,?,?,?,?,?,?,?,?)`;
      let param = [rname, rsex, rsid, rtel, raddress, rphoto, rstate,0];
      let cb = (err, data) => {
        console.log(data);
        console.log(err);
        if (!err) {
          console.log("添加居民基本数据");
          rid = data.insertId;
          resolve({ code: 0, msg: "添加居民成功", data: [] });
        } else {
          reject({ code: 4002, msg: "添加居民失败", data: [] });
        }
      };
      DB.connect(sql, param, cb);
    })
      .then((resData) => {
        return new Promise((resolve, reject) => {
          let sql = `insert into resident_type_detail values `;
          let param = [];
          let teampArr = [];

          rtid.forEach((el) => {
            teampArr.push("(NULL,?,?)");
            param.push(rid, el);
          });
          sql += teampArr.join(",");
          sql += ";";

          let cb = (err, data) => {
            console.log("添加居民类别");
            if (!err) {
              resolve({ code: 0, msg: "添加居民类别成功", data: rid });
            } else {
              resolve({ code: 0, msg: "添加居民类别失败", data: [] });
            }
          };
          DB.connect(sql, param, cb);
        });
      })
      .then((resData) => {
        return new Promise((resolve, reject) => {
          let sql = `insert into team_resident_detail values(NULL,?,?,?,?);`;
          // let date = new Date().getTime();
          let param = [tid, rid, did, new Date().toLocaleDateString()];
          let cb = (err, data) => {
            console.log("添加团队和医生");
            if (!err) {
              resolve({
                code: 0,
                msg: "添加居民选择的团队和医生成功",
                data: [],
              });
            } else {
              reject({
                code: 4002,
                msg: "添加居民选择的团队和医生失败",
                data: [],
              });
            }
          };
          DB.connect(sql, param, cb);
        });
      })
      .then((resData) => {
        console.log(resData);
        return new Promise((resolve, reject) => {
          if (buyService.length === 0) {
            resolve({ code: 0, msg: "添加居民基本信息成功", data: [] });
          } else {
            let sql = `insert into resident_service_detail values `;
            let param = [];
            let arr = buyService;
            let temp = [];
            arr.forEach((el) => {
              temp.push("(NULL,?,?,?)");
              param.push(rid, el.stid, el.slid);
            });
            sql = sql + temp.join(",") + ";";
            let cb = (err, data) => {
              console.log("添加服务包数据");
              if (!err) {
                resolve({ code: 0, msg: "添加居民详细信息成功", data: [] });
              } else {
                resolve({ code: 4002, msg: "添加居民详细信息失败", data: [] });
              }
            };
            DB.connect(sql, param, cb);
          }
        });
      })
      .then((resData) => {
        res.json(resData);
      })
      .catch((resData) => {
        res.json(resData);
      });
  });

/**
 * 获取居民详细列表数据
 */
residentsRouter.route("/residents/infos/list").get((req, res) => {
  let { page, limit, rname, rstate } = req.query;
  page = Number(page);
  limit = Number(limit);
  let startIndex = (page - 1) * limit;
  let param = [];
  let sql = `select * from (select * from residents_table where delstate = '0'`;
  if (rname!=="") {
    sql += ` and rname like ? `;
    param.push("%"+rname+"%");
  }
  if (rstate!=="") {
    sql += ` and rstate = ? `;
    param.push(rstate);
  }
  sql += ` limit ?, ?) as r left join resident_type_detail as rtd on r.rid = rtd.rid join residents_types as rt on rt.rtid = rtd.rtid 
  join team_resident_detail as trd on r.rid = trd.rid join teams as t on trd.tid = t.tid join doctorstable as d on trd.did = d.did;
  select count(*) as count from residents_table where delstate = '0';`;
  param.push(startIndex, limit);
  // console.log(sql,param);

  let cb = (err, data) => {
    console.log(err);
    console.log(data);
    if (!err) {
      res.json({ code: 0, msg: "获取居民详细列表数据成功", data :data[0],count: data[1][0].count});
    } else {
      res.json({ code: 4002, msg: "获取居民详细列表数据失败", data :[]});
    }
  };
  DB.connect(sql, param, cb);
});

/**
 * 删除居民
 * @param {Number} rid 居民ID required
 */
residentsRouter.route("/residents/delete").post((req, res) => {
  let { rid } = req.body;
  rid = Number(rid);
  let sql = "update residents_table set delstate=? where rid=?;";
  let param = ["1", rid];
  let cb = (err, data) => {
    console.log(err);
    if (!err) {
      res.json({ code: 0, msg: "删除居民成功", data: [] });
    } else {
      res.json({ code: 4002, msg: "删除居民失败", data: [] });
    }
  };
  DB.connect(sql, param, cb);
});

/**
 * 获取一个居民购买的服务包信息
 * @param {Number} rid 居民ID required
 */
residentsRouter.route("/residents/buyservice/find").get((req, res) => {
  let { rid } = req.query;
  let sql = `select * from resident_service_detail as rsd ,service_type as st, service_level as sl 
    where rsd.stid = st.stid and rsd.slid = sl.slid and  rsd.rid =?;`;
  let param = [rid];
  let cb = (err, data) => {
    if (!err) {
      res.json({
        code: 0,
        msg: "查询成功",
        data,
      });
    } else {
      res.json({
        code: 4002,
        msg: "查询成功",
        data,
      });
    }
  };
  DB.connect(sql, param, cb);
});

/**
 * 修改居民信息
 *
 */
residentsRouter
  .route("/residents/modify")
  .post(upload.single("rphoto"), (req, res) => {
    let {
      rid,
      rname,
      rsex,
      rsid,
      rtel,
      raddress,
      tid,
      rtid,
      rstate,
      buyService,
      did,
      rphoto,
    } = req.body;
    rtid = JSON.parse(rtid);
    buyService = JSON.parse(buyService);
    rphoto = req.file ? req.file.filename : rphoto;

    // console.log(
    //   rid,
    //   rname,
    //   rsex,
    //   rsid,
    //   rtel,
    //   raddress,
    //   tid,
    //   rtid,
    //   rstate,
    //   buyService,
    //   did,
    //   rphoto
    // );
    new Promise((resolve, reject) => {
      let sql = `DELETE FROM  resident_type_detail WHERE rid = ?;    
      DELETE FROM  team_resident_detail WHERE rid = ?;
      DELETE FROM  resident_service_detail WHERE rid = ?;`;
      let param = [rid, rid, rid];
      let cb = (err, data) => {
        console.log("删除该居民数据");
        if (!err) {
          resolve({ code: 0, msg: "删除该居民数据成功" });
        } else {
          reject({ code: 4002, msg: "删除该居民数据失败" });
        }
      };
      DB.connect(sql, param, cb);
    })
      .then((resData) => {
        return new Promise((resolve, reject) => {
          let sql = `update residents_table set rname =?, rsex =?,rsid=?,rtel=?,raddress=?,rphoto=?,rstate=? where rid=?;`;
          let param = [rname, rsex, rsid, rtel, raddress, rphoto, rstate, rid];
          let cb = (err, data) => {
            console.log(data);
            console.log(err);
            if (!err) {
              console.log("更新居民基本数据");
              resolve({ code: 0, msg: "更新居民成功", data: [] });
            } else {
              reject({ code: 4002, msg: "更新居民失败", data: [] });
            }
          };
          DB.connect(sql, param, cb);
        });
      })
      .then((resData) => {
        return new Promise((resolve, reject) => {
          let sql = `insert into resident_type_detail values `;
          let param = [];
          let teampArr = [];

          rtid.forEach((el) => {
            teampArr.push("(NULL,?,?)");
            param.push(rid, el);
          });
          sql += teampArr.join(",");
          sql += ";";

          let cb = (err, data) => {
            console.log("更新居民类别");
            if (!err) {
              resolve({ code: 0, msg: "更新居民类别成功", data: rid });
            } else {
              reject({ code: 0, msg: "更新居民类别失败", data: [] });
            }
          };
          DB.connect(sql, param, cb);
        });
      })
      .then((resData) => {
        return new Promise((resolve, reject) => {
          let sql = `insert into team_resident_detail values(NULL,?,?,?,?);`;
          // let date = new Date().getTime();
          let param = [tid, rid, did, new Date().toLocaleDateString()];
          let cb = (err, data) => {
            console.log("更新团队和医生");
            if (!err) {
              resolve({
                code: 0,
                msg: "更新居民选择的团队和医生成功",
                data: [],
              });
            } else {
              reject({
                code: 4002,
                msg: "更新居民选择的团队和医生失败",
                data: [],
              });
            }
          };
          DB.connect(sql, param, cb);
        });
      })
      .then((resData) => {
        console.log(resData);
        return new Promise((resolve, reject) => {
          if (buyService.length === 0) {
            resolve({ code: 0, msg: "更新居民基本信息成功", data: [] });
          } else {
            let sql = `insert into resident_service_detail values `;
            let param = [];
            let arr = buyService;
            let temp = [];
            arr.forEach((el) => {
              temp.push("(NULL,?,?,?)");
              param.push(rid, el.stid, el.slid);
            });
            sql = sql + temp.join(",") + ";";
            let cb = (err, data) => {
              console.log("更新服务包数据");
              if (!err) {
                resolve({ code: 0, msg: "更新居民详细信息成功", data: [] });
              } else {
                resolve({ code: 4002, msg: "更新居民详细信息失败", data: [] });
              }
            };
            DB.connect(sql, param, cb);
          }
        });
      })
      .then((resData) => {
        res.json(resData);
      })
      .catch((resData) => {
        res.json(resData);
      });
  });


  /**
   * 签约统计
   * @param {String} tname 团队名称 模糊查询 
   * @param {String} startDate 查询的签约起始时间 格式： 'yyyy-mm-dd'
   * @param {String} endDate 查询的签约结束时间 格式： 'yyyy-mm-dd'
   * 
   */
  residentsRouter.route('/residents/statistical')
  .get((req, res)=>{
    let {tname, startDate, endDate} = req.query;
    let sql = `SELECT 
    t.tid,tname,trd.date,rtd.rtid,rt.rtname,r.rid,r.rname,r.rsid
  FROM
    teams AS t 
   LEFT JOIN  team_resident_detail AS trd ON t.tid = trd.tid
   LEFT JOIN residents_table AS r  ON r.rid = trd.rid
   LEFT JOIN  resident_type_detail AS rtd ON r.rid = rtd.rid
   LEFT JOIN  residents_types AS rt ON rt.rtid = rtd.rtid
   WHERE t.tdeleteState='0' AND r.delstate='0'`;
   let param=[];
   if(tname){
    sql += ` AND tname LIKE ?`
    param.push("%"+tname+"%")
   }
   if(startDate&&endDate){
    sql += ` AND trd.date BETWEEN ? AND ?`
    param.push(startDate,endDate)
   }
   sql +=";"
   let cb=(err, data)=>{
    if (!err) {
      res.json({code:0,msg:"查询成功",data})
    } else {
      res.json({code:4002,msg:"查询失败",data:[]})
    }
   }
   DB.connect(sql, param, cb)
  })